﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Reflection;
using System.Data;
using System.Data.SQLite;
using System.ComponentModel;

namespace SQLiteDB
{
   public class SQLiteORM
    {

        private static volatile SQLiteORM instance;
        private static object lockHelper = new object();  
        //单例模式
        public static SQLiteORM Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (lockHelper)
                    {
                        if (instance == null)
                        {
                            instance = new SQLiteORM();
                        }
                    }
                }
                return instance;
            }
        }

        #region 公共变量
        public string dbPath;
        public string DataSource;
        #endregion 
        private SQLiteORM()
        {
            dbPath=Environment.CurrentDirectory + @"\MyDB.db";
            DataSource = "Data Source=" + dbPath;
            if (!File.Exists(dbPath))
            {
                System.Data.SQLite.SQLiteConnection.CreateFile(dbPath);
            }
         
        }


        /// <summary>
        /// 创建表
        /// </summary>
        public void  CreateTable<T>(T t)
        {
             using (SQLiteConnection conn = new SQLiteConnection(DataSource))
             {
                 conn.Open();
                 SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                 string tableName = t.GetType().Name;
                 if (!sQLiteBase.ExistsTable(tableName))
                 {
                     SQLiteTable sqliteTable = new SQLiteTable();
                     sqliteTable.TableName = tableName;
                     foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
                     {
                         bool IsKey = false;
                         var attribute = info.GetCustomAttributes(typeof(DescriptionAttribute), false).FirstOrDefault();
                         if (attribute != null && ((DescriptionAttribute) attribute).Description == "KEY")
                         {
                             IsKey = true;
                         }
                         if (info.PropertyType == typeof(string))
                         {
                             sqliteTable.Columns.Add(new SQLiteColumn(info.Name, ColType.VARCHAR, IsKey));
                         }
                         else if (info.PropertyType == typeof(int))
                         {
                             sqliteTable.Columns.Add(new SQLiteColumn(info.Name, ColType.Int, IsKey));
                           
                         }
                        else if (info.PropertyType == typeof(long))
                        {
                            sqliteTable.Columns.Add(new SQLiteColumn(info.Name, ColType.Int, IsKey));

                        }
                        else if (info.PropertyType == typeof(decimal))
                         {
                             sqliteTable.Columns.Add(new SQLiteColumn(info.Name, ColType.Decimal, IsKey));
                         }
                         else if (info.PropertyType == typeof(bool))
                         {
                             sqliteTable.Columns.Add(new SQLiteColumn(info.Name, ColType.BLOB, IsKey));
                         }
                         else if (info.PropertyType == typeof(DateTime))
                         {
                             sqliteTable.Columns.Add(new SQLiteColumn(info.Name, ColType.DateTime, IsKey));
                         }
                     }
                     sQLiteBase.CreateTable(sqliteTable);
                 }
             }

        }

        public bool AddColumn(string tableName ,string  colName)
        {
            string sql = $"ALTER TABLE {tableName} ADD COLUMN {colName} text";
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();
                    sQLiteBase.BeginTransaction();
 
                    sQLiteBase.Execute(sql);
                    sQLiteBase.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    sQLiteBase.Rollback();
                    return false;
                }
            }
        }
         
       /// <summary>
       /// 插入单条数据
       /// </summary>
        public bool Insert<T>(T t)
        {

            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                try
                {
                    conn.Open();
                    SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                    string tableName = t.GetType().Name;
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
                    {
                        dic.Add(info.Name, info.GetValue(t, null));
                    }
                    sQLiteBase.Insert(tableName, dic);

                }
                catch(Exception ex) {
                    return false;
                }
            }
            return true;
        }


        /// <summary>
        /// 批量插入
        /// </summary>
        public bool InsertListByTransaction<T>(List<T> list) where T : new()
        {
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();
                    sQLiteBase.BeginTransaction();
                    //foreach (T t in list)
                    //{
                    //    string tableName = t.GetType().Name;
                    //    Dictionary<string, object> dic = new Dictionary<string, object>();
                    //    foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
                    //    {
                    //        dic.Add(info.Name, info.GetValue(t, null));
                    //    }
                    //    sQLiteBase.Insert(tableName, dic);
                    //}

                    sQLiteBase.InsertPatch<T>(list);
                    sQLiteBase.Commit();
                }
                catch(Exception ex)
                {
                    sQLiteBase.Rollback();
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        public bool InsertList <T>(List<T> list ,out string  mes) where T : new()
        {
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();
                    sQLiteBase.BeginTransaction();
                    //foreach (T t in list)
                    //{
                    //    string tableName = t.GetType().Name;
                    //    Dictionary<string, object> dic = new Dictionary<string, object>();
                    //    foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
                    //    {
                    //        dic.Add(info.Name, info.GetValue(t, null));
                    //    }
                    //    sQLiteBase.Insert(tableName, dic);
                    //}
                    

                     sQLiteBase.InsertPatch<T>(list);
                     sQLiteBase.Commit();
                }
                catch (  SQLiteException exception)
                {
                    sQLiteBase.Rollback();
                    mes = exception.Message;
                    //constraint failed
                    //UNIQUE constraint failed: CTNCode.Code
                    return false;
                }
            }
            mes = "";
            return true;
        }



        /// <summary>
        /// 删除
        /// </summary>
        public bool Delete(string  sql)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();
                    sQLiteBase.Execute(sql,null);
                   
                }
                catch (Exception ex){ 
                return false;
                
                }
            }
            return true;
        }



        /// <summary>
        /// 删除
        /// </summary>
        public bool Delete<T>(T t, params string [] ss)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();
                    string tableName = t.GetType().Name;
                    Dictionary<string, object> dicCond = new Dictionary<string, object>();
                    foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
                    {
                        if (ss.Contains(info.Name))
                        {
                            dicCond.Add(info.Name, info.GetValue(t, null));
                        }
                    }
                    sQLiteBase.Delete(tableName,  dicCond);
                }
                catch
                {
                    return false;

                }
            }
            return true;
        }

        /// <summary>
        /// 更新
        /// </summary>
        public bool Update (string sql)
        {
           using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();
                    sQLiteBase.Execute(sql,null);
                   
                }
                catch { 
                return false;
                
                }
            }
            return true;
            
        }



        /// <summary>
        /// 更新
        /// </summary>
        public bool Update<T>(T t, params string [] ss)
        {    
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();

                    string tableName = t.GetType().Name;

                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    Dictionary<string, object> dicCond=new Dictionary<string,object>();
                    foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
                    {
                        if(ss.Contains(info.Name))
                        {
                        dicCond.Add(info.Name, info.GetValue(t, null));
                        }
                        else{
                         dic.Add(info.Name, info.GetValue(t, null));
                        }
                    }
                    sQLiteBase.Update(tableName, dic, dicCond);
                }
                catch(Exception ex)
                {
                    return false;

                }
            }
            return true;
        
        }

        /// <summary>
        /// 查询
        /// </summary>
        public DataTable GetDataTable(string sql)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();

                  return  sQLiteBase.Select(sql);
                }
                catch
                {
                    return null;
                }


            }
              
        }

        /// <summary>
        /// 查询
        /// </summary>
        public DataTable GetDataTable(string sql,IEnumerable<SQLiteParameter> parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();

                    return sQLiteBase.Select(sql, parameters);
                }
                catch
                {
                    return null;
                }
            }

        }
        
       /// <summary>
       /// 单个实体
       /// </summary>
        public T GetEntity<T>(string sql) where T : new() 
        {
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();

                    return sQLiteBase.ExecuteList<T>(sql).FirstOrDefault(); 
                }
                catch
                {
                    return default(T);
                }
            }
        }

        /// <summary>
        /// 实体集合
        /// </summary>
        public List<T> GetEntityList<T>(string sql) where T : new() 
        {
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                try
                {
                    conn.Open();
                    return sQLiteBase.ExecuteList<T>(sql); 
                }
                catch (Exception ex)
                {
                    return  null;
                }
            }

        }




        /// <summary>
        /// dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="conn">数据库连接池连接对象</param>
        /// <param name="files">列</param>
        /// <param name="tableName">表</param>
        /// <param name="where">条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public IEnumerable<T> GetPageList<T>(string cols, string tableName, string where, string orderby, int pageIndex, int pageSize, out int total, out int pageCount) where T : new()
        {
            total = 0;
            pageCount = 0;
            using (SQLiteConnection conn = new SQLiteConnection(DataSource))
            {
                SQLiteBase sQLiteBase = new SQLiteBase(conn.CreateCommand());
                int skip = 0;
                if (pageIndex > 0)
                {
                    skip = (pageIndex - 1) * pageSize + 0;
                }
                try
                {
                    conn.Open();

                    object res=  sQLiteBase.ExecuteScalar(string.Format("SELECT COUNT(1) FROM {0} where {1};", tableName, where));
                    total = int.Parse(res.ToString());
                    pageCount = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
                    StringBuilder sb = new StringBuilder();

                    sb.AppendFormat(@"SELECT  {0} FROM  {1} Where  {2} Order By {3}  limit {4} offset {5}", cols, tableName, where, orderby, pageSize, skip);
                    return sQLiteBase.ExecuteList<T>(sb.ToString());
                }
                catch (Exception ex)
                {

                    return null;
                }

            }
        }

    }
}
